package com.xingxuecn.limit.dao.impl;

import com.xingxuecn.limit.common.model.dto.Role;
import com.xingxuecn.limit.common.model.dto.User;
import com.xingxuecn.limit.common.model.vo.Page;
import com.xingxuecn.limit.common.utils.DbUtils;
import com.xingxuecn.limit.dao.UserDao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * @author Kris at 2018/9/13 10:42
 */
public class UserDaoImpl implements UserDao {
    QueryRunner queryRunner = new QueryRunner(DbUtils.getDataSource());

    @Override
    public Page<User> searchPage(User user, Integer page, Integer rows) throws SQLException {
        return  new Page<User>(this.count(user),this.search(user,new Integer[]{(page-1)*rows,rows}));
    }

    @Override
    public int count(User user) throws SQLException {
        StringBuffer sql = new StringBuffer("select count(*) from user where 1=1");
        List<Object> jdbcParams = new LinkedList<>();
        if (user.getId() != null) {
            sql.append(" and id = ?");
            jdbcParams.add(user.getId());
        }
        if (StringUtils.isNotBlank(user.getNick())) {
            sql.append(" and nick like ?");
            jdbcParams.add("%" + user.getNick() + "%");
        }
        if(StringUtils.isNotBlank(user.getAccount())){
            sql.append(" and account = ?");
            jdbcParams.add(user.getAccount());
        }
        if(StringUtils.isNotBlank(user.getPwd())){
            sql.append(" and pwd = ?");
            jdbcParams.add(user.getPwd());
        }

        Long result = queryRunner.query(sql.toString(), new ScalarHandler<Long>(), jdbcParams.toArray());
        return result.intValue();
    }

    @Override
    public List<User> search(User user, Integer... limit) throws SQLException {
        StringBuffer sql = new StringBuffer("select * from user where 1=1");
        List<Object> jdbcParams = new LinkedList<>();
        if (user.getId() != null) {
            sql.append(" and id = ?");
            jdbcParams.add(user.getId());
        }
        if (StringUtils.isNotBlank(user.getNick())) {
            sql.append(" and nick like ?");
            jdbcParams.add("%" + user.getNick() + "%");
        }
        if(StringUtils.isNotBlank(user.getAccount())){
            sql.append(" and account = ?");
            jdbcParams.add(user.getAccount());
        }
        if (limit != null && limit.length != 0) {
            if(limit.length == 1){
                sql.append(" limit ?");
                jdbcParams.add(limit[0]);
            }else if(limit.length == 2){
                sql.append(" limit ?,?");
                jdbcParams.add(limit[0]);
                jdbcParams.add(limit[1]);
            }else{
                throw new SQLException("limit最大长度为2");
            }
        }
        List<User> result = queryRunner.query(sql.toString(), new BeanListHandler<User>(User.class), jdbcParams.toArray());
        return result;
    }

    @Override
    public int add(User user) throws SQLException {
        String sql = "insert into user(nick,account,pwd,state) values(?,?,?,?)";
        return queryRunner.execute(sql, new Object[]{
                user.getNick(),
                user.getAccount(),
                user.getPwd(),
                user.getState()
        });
    }

    @Override
    public int update(User user) throws SQLException {
        String sql = "update user set nick=?,account=?,pwd=?,state=? where id=?";
        return queryRunner.update(sql, new Object[]{
                user.getNick(),
                user.getAccount(),
                user.getPwd(),
                user.getState(),
                user.getId()
        });
    }

    @Override
    public int delete(Integer id) throws SQLException {
        String sql = "delete from user where id = ?";
        return queryRunner.execute(sql,id);
    }
}
